package com.teacher.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.multipart.MultipartFile;

import com.common.model.Project;
import com.common.model.TeacherUser;
import com.teacher.model.ProjAndProf;
import com.teacher.service.TeacherService;

/**
 * Excel工具类
 * 
 * @author XHFSJ
 * 
 */
public class ExcelUtils {

	private static final Logger log = org.slf4j.LoggerFactory
			.getLogger(ExcelUtils.class);

	@Autowired
	private TeacherService tservice;
	/**
	 * 获取文件的扩展名
	 * 
	 * @param filename
	 *            包含绝对路径的文件名
	 * @return
	 * @throws Exception
	 */
	public static String getFileExtendName(String filename) throws Exception {
		String[] strs = filename.split("\\.");
		String extName = strs[strs.length - 1];
		if (strs.length == 1)
			extName = "";

		return extName;
	}

	/**
	 * 读取excel文件中的数据
	 * 文件中的数据排列顺序：名字、描述、备注、可选专业
	 * @param FileName
	 *            源文件名
	 * @param isXLSX
	 *            是否是xlsx类型的office excel文件
	 * @return
	 * @throws Exception
	 * @throws IOException
	 */
	@SuppressWarnings("deprecation")
	public static Collection<Project> ReadExcel(String FileName,
			boolean isXLSX,List<String> columnNames,
			TeacherService tservie,HttpServletRequest request)
			throws Exception, IOException {
		System.out.println(columnNames);
		Collection<Project> projects = null;
		try {
			if (isXLSX) {
				projects = new ArrayList<>();
				CheckFile(FileName, "xlsx");
				XSSFWorkbook wb = new XSSFWorkbook(FileName);
				XSSFSheet sheet = wb.getSheetAt(0);
				int rowCount = sheet.getPhysicalNumberOfRows();
				XSSFRow row0 = sheet.getRow(0);
				Iterator<Cell> cells0 = row0.cellIterator();
				Map<String,Integer> colNames = new HashMap<>();
				int index = 0;
				while(cells0.hasNext()){
					String colName = getContent(cells0.next());
					if(columnNames.contains(colName)){
						colNames.put(colName, index);
						index++;
					}
				}
				for (int i = 1; i < rowCount; i++) {
					Iterator<Cell> cells = sheet.getRow(i).iterator();
					Cell c = null;
					List<String> params = new ArrayList<>();
					while (cells.hasNext()) {
						c = cells.next();
						params.add(getContent(c));
					}
					Project project = new Project();
					if(columnNames.contains("题目描述"))
						StringUtils.setProperties(project, "description", params, colNames.get("题目描述"));
					if(columnNames.contains("题目名称"))
						StringUtils.setProperties(project, "name", params, colNames.get("题目名称"));
					if(columnNames.contains("备注"))
						StringUtils.setProperties(project, "remark", params, colNames.get("备注"));
					if(columnNames.contains("评阅教师")){
						String syteacher = params.get(colNames.get("评阅教师"));
						TeacherUser user = (TeacherUser)request.getSession().getAttribute("user");
						TeacherUser teacher = tservie.getTeacherInfoByName(syteacher, user.getDepartmentId());
						
						project.setShengyue_tea_id(teacher.getId());
					}
					if(columnNames.contains("可选专业"))
					{
						//处理可选专业
						String profName = params.get(colNames.get("可选专业"));
						List<ProjAndProf> profs = new ArrayList<>();
						String[] profNames = profName.split("[,、; ]");
						for(String s : profNames){
							Integer id = tservie.getProfessionIDByName(s);
							ProjAndProf pap = new ProjAndProf();
							pap.setProf_id(id);
							profs.add(pap);
						}
						project.setProfs(profs);
					}
					projects.add(project);
//					System.out.println();
				}

			} else {
				projects = ReadExcel(FileName,tservie,columnNames,request);
			}
		} catch (Exception e) {
			log.info(e.getLocalizedMessage());
			throw e;
		}

		return projects;
	}

	/**
	 * 獲取單元格中的值的字符串
	 * 
	 * @param c
	 * @return
	 */
	private static String getContent(Cell c) {
		String result = null;
		try {
			result = c.getCellFormula();
		} catch (RuntimeException e) {
			try {
				result = c.getStringCellValue();
			} catch (RuntimeException e1) {
				try {
					result = String.valueOf(c.getBooleanCellValue());
				} catch (RuntimeException e2) {
					try {
						result = String.valueOf(c.getNumericCellValue());
					} catch (RuntimeException e3) {
						try {
							result = String.valueOf(c.getDateCellValue());
						} catch (RuntimeException e4) {
							try {
								result = String.valueOf(c
										.getRichStringCellValue());
							} catch (RuntimeException e5) {
							}
						}

					}

				}
			}
		}
		return result.trim();
	}

	/**
	 * 检查文件的类型的合法性
	 * 
	 * @param filename
	 *            待检查的文件名
	 * @param extName
	 *            需要匹配的文件扩展名
	 * @throws Exception
	 */
	private static boolean CheckFile(String filename, String extName)
			throws Exception {
		if (!extName.equals(getFileExtendName(filename))) {
			log.info("文件类型错误！");
			throw new RuntimeException("文件类型错误！");
		}
		return true;
	}

	/**
	 * 从指定的文件中读取数据（第二行开始），将数据保存到一个集合中 以便于其他函数调用，进行数据进一步处理
	 * 
	 * @param filename
	 *            excel 2003及以下文件所在位置，若该文件不存在，将抛出IllegalArgumentException
	 * @return 返回值为一个存有Project类型对象的Collection集合，是excel文件中的所有Project类型数据
	 * @throws Exception
	 */
	private static Collection<Project> ReadExcel(String filename,TeacherService tservice,List<String> columnNames,HttpServletRequest request)
			throws Exception {
		File file = new File(filename);
		CheckFile(filename, "xls");
		/**
		 * Project对象的集合，用于保存从excel文件中读出的数据
		 */
		Collection<Project> projects = new ArrayList<Project>();
		try {
			Workbook wb = Workbook.getWorkbook(file);
			// 获取excel表中的所有工作表
			Sheet[] sheets = wb.getSheets();
			/**
			 * 检查excel中是否有工作表存在，若不存在， 将抛出参数非法异常——工作表中无数据
			 */
			if (sheets.length < 0 || sheets == null)
				log.error("not exists data in this file!");
			for (int i = 0; i < sheets.length; i++) {
				if (sheets[i].getRows() < 1) {
					log.warn("data not enough!");
					// throw new IllegalArgumentException("");
				}
			}
			Project project = null;
			for (int i = 0; i < sheets.length; i++) {
				jxl.Cell[] cell0 = sheets[i].getRow(0);
				Map<String,Integer> colNames = new HashMap<>();
				for(int index = 0; index < cell0.length; index++){
					colNames.put(cell0[index].getContents(), index);
				}
				for (int j = 1; j < sheets[i].getRows(); j++) {
					// 获取当前行的所有列
					jxl.Cell[] project_data = sheets[i].getRow(j);
					List<String> params = new ArrayList<>();
					for (jxl.Cell c : project_data) {
						params.add(c.getContents());
					}
					while(params.size()<4){
						params.add(null);
					}
					project = new Project();
					if(columnNames.contains("题目描述"))
						StringUtils.setProperties(project, "description", params, colNames.get("题目描述"));
					if(columnNames.contains("题目名称"))
						StringUtils.setProperties(project, "name", params, colNames.get("题目名称"));
					if(columnNames.contains("备注"))
						StringUtils.setProperties(project, "remark", params, colNames.get("备注"));
					if(columnNames.contains("评阅教师")){
						String syteacher = params.get(colNames.get("评阅教师"));
						TeacherUser user = (TeacherUser)request.getSession().getAttribute("user");
						System.out.println(syteacher+"."+user.getDepartmentId());
						TeacherUser teacher = tservice.getTeacherInfoByName(syteacher, user.getDepartmentId());
						System.out.println(teacher.getName());
						
						project.setShengyue_tea_id(teacher.getId());
					}
					if(columnNames.contains("可选专业"))
					{
						//处理可选专业
						String profName = params.get(colNames.get("可选专业"));
						List<ProjAndProf> profs = new ArrayList<>();
						String[] profNames = profName.split("[,、;， ]");
						for(String s : profNames){
							Integer id = tservice.getProfessionIDByName(s);
							ProjAndProf pap = new ProjAndProf();
							pap.setProf_id(id);
							profs.add(pap);
						}
						project.setProfs(profs);
					}
					/**
					 * 将初始化后的project对象添加到集合中
					 */
					projects.add(project);
				}
			}
			wb.close();
		} catch (BiffException | IOException e) {
			throw e;
		}

		return projects;
	}

	/**
	 * 上传文件到指定目录
	 * 
	 * @param source
	 *            要上传的MultipartFile
	 * @param destDir
	 *            要上传到的目标目录
	 * @return 上传成功则返回文件在服务器上的路径，否则抛出运行时异常
	 */
	public static String Upload(MultipartFile source, String destDir) {
//		System.out.println("destDir:" + destDir);
		try {
			String fileType = getFileExtendName(source.getOriginalFilename());
			if (!fileType.equals("xls") && !fileType.equals("xlsx")) {
				log.error("请选择正确的excel文件");
				throw new RuntimeException("文件类型错误");
			}
			File dir = new File(destDir);
			if (!dir.exists()) {
				dir.mkdirs();
			}
			destDir += File.separator+source.getOriginalFilename();
			source.transferTo(new File(destDir));
			return destDir;

		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getLocalizedMessage());
			throw new RuntimeException("上传错误！");
		}
	}
}
